package com.eling.elcms.travel.dao.hibernate;


import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.core.util.PropertyUtils;
import com.eling.elcms.travel.dao.ICustomerDao;
import com.eling.elcms.travel.model.Customer;
import com.eling.elcms.travel.model.view.QueryAllIntentionView;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class CustomerDaoHibernate extends GenericDaoHibernate<Customer, Long> implements ICustomerDao {

	public CustomerDaoHibernate() {
		super(Customer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> queryAllPkCustomer(Customer cond) {
		SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT	");
		sql.append("  		tc.pkCustomer	");
		sql.append("  	FROM  travel_customer tc	");
		sql.append("  	LEFT OUTER JOIN travel_producttype tpt ON tpt.pkProductType = tc.pkProductType	");
		sql.append("  	LEFT OUTER JOIN travel_product tp ON tpt.pkProduct = tp.pkProduct	");
		sql.append("  	LEFT OUTER JOIN sm_user smu ON smu.pkUser = tc.pkSaleConsultant	");
		sql.append("  	WHERE 1=1 	");
		if(null != cond.getProductType()){
			if(null != cond.getProductType().getPkProductType()){
				sql.append("  	AND tpt.pkProductType='" + cond.getProductType().getPkProductType() + "'	");
			}else if(null != cond.getProductType().getProduct()){
				sql.append("  	AND tp.pkProduct='" + cond.getProductType().getProduct().getPkProduct() + "'	");
			}
		}
		if(null != cond.getDataSource()){
			sql.append("  	AND tc.dataSource ='" + cond.getDataSource() + "'	");
		}
		if(null != cond.getIntention()){
			sql.append("  	AND tc.intention = '" + cond.getIntention().name() + "'	");
		}
		if(null != cond.getStatus()){
			sql.append("  	AND tc.`status` = '" + cond.getStatus().name()+ "'	");
		}
		if(cond.getSaleConsultant() !=null){
			sql.append("  	AND smu.pkUser="+cond.getSaleConsultant().getPkUser()+"	");
		}
		if(cond.getReturnVisitTime()!=null){
			sql.append("  	AND (tc.returnVisitTime IS NULL  	");
			sql.append("  		OR tc.returnVisitTime >='"+sFormat.format(cond.getReturnVisitTime())+"'	");
			sql.append(
					"  		AND tc.returnVisitTime <='"+sFormat.format((Date)PropertyUtils.getProperty(cond, "returnVisitTimeEnd"))+"'	");
			sql.append("  		);	");
		}
		List<Object> objList = getSession().createNativeQuery(sql.toString()).list();
		
		List<Long> pkList = new ArrayList<Long>();
		if(!objList.isEmpty()){
			for(int i = 0; i < objList.size(); i++){
				Long pk = Long.valueOf(objList.get(i).toString());
				pkList.add(pk);
			}
		}
		
		return pkList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<QueryAllIntentionView> queryAllIntention(Customer cond) {
		
		StringBuffer sql = new StringBuffer();
		
		
		sql.append(" SELECT ");
		//sql.append(" tc.pkCustomer,");
		sql.append(" tpt.pkProductType,");
		sql.append(" tpt.name as tptName,");
		sql.append(" tp.pkProduct,");
		sql.append(" tp.name as tpName,");
		sql.append(" SUM(CASE WHEN tc.intention = 'NoInterest' AND tc.status != 'Contract' THEN 1 ELSE 0 END) AS noInterestNumber,");
		sql.append(" SUM(CASE WHEN tc.intention = 'SameAs' AND tc.status != 'Contract' THEN 1 ELSE 0 END) AS sameasNumber,");
		sql.append(" SUM(CASE WHEN tc.intention = 'Interest' AND tc.status != 'Contract' THEN 1 ELSE 0 END) AS interestNumber,");
		sql.append(" SUM(CASE WHEN tc.intention = 'WantToBuy' AND tc.status != 'Contract' THEN 1 ELSE 0 END) AS wanttobuyNumber,");
		sql.append(" SUM(CASE WHEN tc.status = 'Contract' AND tc.status != 'Contract' THEN 1 ELSE 0 END) AS contractNumber ");
		sql.append(" FROM ");
		sql.append(" travel_customer tc ");
		sql.append(" LEFT JOIN travel_producttype tpt ON tpt.pkProductType = tc.pkProductType ");
		sql.append(" LEFT JOIN travel_product tp ON tp.pkProduct = tpt.pkProduct ");
		sql.append(" WHERE ");
		//sql.append(" visitTime BETWEEN "+startTime+"AND"+endTime);
		sql.append("  	(tc.visitTime IS NULL  	");
		sql.append("  		OR tc.visitTime >=:visitTime	");
		sql.append("  		AND tc.visitTime <=:visitTimeEnd	");
		sql.append("  		)	");
		sql.append(" AND tc.pkProductType IS NOT NULL ");
		sql.append(" GROUP BY tpt.pkProductType");
		
		List<Object[]> objList = getSession().createNativeQuery(sql.toString())
				.setParameter("visitTime", PropertyUtils.getProperty(cond,"visitTime"))
				.setParameter("visitTimeEnd",PropertyUtils.getProperty(cond, "visitTimeEnd")).list();
		
		List<QueryAllIntentionView> qaViewList = new ArrayList<>();
		for (Object[] objects : objList) {
			if(objects[2]!=null && objects[1]!=null && !"".equals(objects[1])) {
				QueryAllIntentionView queryAllIntentionView = new QueryAllIntentionView();
				
				queryAllIntentionView.setPkProductType(objects[0].toString());
				queryAllIntentionView.setProductTypeName(objects[1].toString());
				queryAllIntentionView.setPkProduct(objects[2].toString());
				queryAllIntentionView.setProductName(objects[3].toString());
				queryAllIntentionView.setNoInterestNumber(objects[4].toString());
				queryAllIntentionView.setSameasNumber(objects[5].toString());
				queryAllIntentionView.setInterestNumber(objects[6].toString());
				queryAllIntentionView.setWantToBuyNumber(objects[7].toString());
				queryAllIntentionView.setContractNumber(objects[8].toString());
				qaViewList.add(queryAllIntentionView);
			}
		}
		return qaViewList;		
	}

	@SuppressWarnings("unchecked")
	public List<Customer> queryByIntention(Customer cond) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" tc.pkCustomer,bp.name as personName,bp.phone,tpt.name as productName, ");
		sql.append(" tc.intention,tc.status,tc.visitcount,tc.returnvisittime,su.name as suname ");
		sql.append(" FROM ");
		sql.append(" travel_customer tc LEFT JOIN bd_personalinfo bp on tc.pkPersonalInfo=bp.pkPersonalInfo ");
		sql.append(" LEFT JOIN travel_producttype tpt ON tpt.pkProductType=tc.pkProductType ");
		sql.append(" LEFT JOIN travel_product tp ON tpt.pkProduct=tp.pkProduct ");
		sql.append(" LEFT JOIN sm_user su on tc.pkSaleConsultant=su.pkUser ");
		sql.append(" WHERE (tc.intention is not null or tc.intention=:selectIntention) ");
		sql.append(" AND (bp.name is not null or bp.name=:personName)");
		
		List<Customer> cusList = getSession().createNativeQuery(sql.toString())
				.setParameter("selectIntention", cond.getIntention())
				.setParameter("personName", cond.getPersonalInfo().getName()).list();
		return cusList;
	}
}
